package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.common.dbutil.page.Page;
import com.eduction.modules.system.entity.CityEntity;
import com.eduction.modules.system.entity.School;
import com.eduction.modules.system.entity.SchoolType;

@Repository
public class SchoolRepository {

	@Autowired
	private DbHelper dbHelper;
	@Autowired
	private SchoolDao schoolDao;
	
	public School querySchoolByAccount(String account) throws SQLException{
		String sql = "select * from t_school where p_id = ?";
		return dbHelper.getBean(sql, School.class, account);
	}
	
	public void saveSchool(School s) throws SQLException{
		String sql = "insert into t_school(p_id,s_name,p_type_id,p_city_id,p_password,year)values(?,?,?,?,?,?)";
		dbHelper.execute(sql, Integer.valueOf(s.getP_id()),s.getS_name(),s.getP_type_id(),s.getP_city_id(),s.getP_password(),s.getYear());
//		schoolDao.save(s);
	}

	public void updateState(School s) throws SQLException{
		String sql = "update t_school set s_state= ? where p_id= ? ";
		dbHelper.execute(sql, s.getS_state(),Integer.valueOf(s.getP_id()));
	}

	public void resetPassword(String id,String password) throws SQLException{
		String sql = "update t_school set p_password= ? where p_id= ? ";
		dbHelper.execute(sql, password,Integer.valueOf(id));
//		schoolDao.save(s);
	}
	
	/**
	 * 分页查询学校
	 * @throws SQLException 
	 * @throws NumberFormatException 
	 */
	public Page<School> querySchoolByPage(Map<String ,String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("select a.*, b.s_name as cityName from t_school as a left join t_sys_city as b on a.p_city_id = b.p_id"
				+ " where 1=1");
		if(StringUtils.isNotBlank(map.get("name"))){
			sql.append(" and a.s_name like '%"+map.get("name").trim()+"%'");
		}
		if(StringUtils.isNotBlank(map.get("cityId"))){
			sql.append(" and a.p_city_id = "+Long.valueOf(map.get("cityId"))+"");
		}
		if(StringUtils.isNotBlank(map.get("typeId"))){
			sql.append(" and a.p_type_id = "+Long.valueOf(map.get("typeId"))+"");
		}
		if(StringUtils.isNotBlank(map.get("major"))){
			sql.append(" and a.year like '%"+map.get("major").trim()+"%'");
		}

		return dbHelper.getPage(sql.toString(), School.class,Integer.valueOf(map.get("pageNumber")),Integer.valueOf(map.get("pageSize")));
		
	}

	/**
	 * 查询未绑定专家组的学校
	 */
	public List<School> querySchoolByYear(String year) throws SQLException{
		String sql = "select * from t_school where year = "+year+"  ORDER BY CONVERT( s_name USING gbk ) COLLATE gbk_chinese_ci ASC";
		return this.dbHelper.getBeanList(sql, School.class);
	}
	
	/**
	 * 查询江苏省所有城市
	 * @return
	 * @throws SQLException
	 */
	public List<CityEntity> queryCities() throws SQLException{
		String sql = "select * from t_sys_city where f_id=?";
		List<CityEntity> cities = dbHelper.getBeanList(sql, CityEntity.class, "1495071558649000");
		return cities;
	}
	
	/**
	 * 查询学校类型
	 * @return
	 * @throws SQLException
	 */
	public List<SchoolType> querySchoolType() throws SQLException{
		String sql = "select * from t_school_type";
		List<SchoolType> types = dbHelper.getBeanList(sql, SchoolType.class, null);
		return types;
	}
	
	
	/**
	 * 根据id删除学校
	 * @param pid
	 */
	public void deleteSchool(String pid){
		schoolDao.delete(pid);
	}
	
	/**
	 * 查询未绑定专家组的学校
	 */
	public List<School> querySchoolNotBand() throws SQLException{
		String sql = "select p_id as p_id ,s_name as s_name from t_school where isband = 0  ORDER BY CONVERT( s_name USING gbk ) COLLATE gbk_chinese_ci ASC";
		return this.dbHelper.getBeanList(sql, School.class);
	}

	/**
	 * 查询未绑定专家组的学校
	 */
	public List<School> querySchoolNotBand2() throws SQLException{
		String sql = "select p_id as p_id ,s_name as s_name from t_school where isband2 = 0 ORDER BY CONVERT( s_name USING gbk ) COLLATE gbk_chinese_ci ASC";
		return this.dbHelper.getBeanList(sql, School.class);
	}
	
	/**
	 * 查询某个专家所在的组绑定的学校
	 */
	public List<School> querySchoolByExpert(String expertId) throws SQLException{
		String sql = "select s.p_id as p_id ,s.s_name as s_name,f.s_state as s_write from t_school as s "
			+" LEFT JOIN t_school_group_map as g on s.p_id = g.s_school_id "
			+" LEFT JOIN t_expert_group_map as m on g.s_group_id = m.s_group_id "
			+" LEFT JOIN t_feedback as f on s.p_id = f.s_school_id "
			+" WHERE m.s_expert_id = ?";
		return this.dbHelper.getBeanList(sql, School.class, expertId);
	}
	
	/**
	 * 根据id查学校名称
	 * @throws SQLException 
	 */
	public School queryNameById(String id) throws SQLException{
		String sql = "select p_id as p_id,s_name as s_name from t_school where p_id = ?";
		return this.dbHelper.getBean(sql, School.class, id);
	}
	
	/**
	 * 查询单个学校
	 */
	public School querySchoolById(String id) throws SQLException{
		String sql = "select s.*, t.s_name as typeName from t_school as s left join t_school_type as t on s.p_type_id = t.p_id where s.p_id = ?";
		return dbHelper.getBean(sql, School.class, id);
		
	}
	
	/**
	 * 根据id查学校
	 * @throws SQLException 
	 */
	public School queryById(String id) throws SQLException{
		String sql = "select * from t_school where p_id = ?";
		return this.dbHelper.getBean(sql, School.class, id);
	}

	public List<School> queryBySchoolType(String schoolType) throws SQLException {
		String sql = "select * from t_school where p_type_id = ?";
		return this.dbHelper.getBeanList(sql, School.class, schoolType);
	}

	public void resetSchoolStateBySchool(String schoolId) throws SQLException  {
		String sql = "update t_school set s_state= 1 where p_id=?";
		this.dbHelper.execute(sql, schoolId);

	}

	public List<School> querySchoolByExpertOne(String expertId) throws SQLException {

		String sql = "select * from t_school WHERE p_id in " +
				"(SELECT s_school_id from t_school_group_map where s_group_id in" +
				"(SELECT t.s_group_id from t_expert_group_map t WHERE t.s_expert_id=?)) " +
				"and isband=1";

//		String sql = "SELECT * from t_school where p_id in(SELECT t.s_school_id " +
//				"from t_expert_item_map t WHERE t.s_expert_id=? " +
//				" AND t.s_type=2 GROUP BY t.s_school_id);";
		return this.dbHelper.getBeanList(sql, School.class, expertId);
	}
}
